package com.threeox.ormlibrary.impl;

import android.annotation.TargetApi;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;
import android.os.Message;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.threeox.ormlibrary.annotation.create.Column;
import com.threeox.ormlibrary.annotation.create.Primary;
import com.threeox.ormlibrary.db.OrmDatabaseManager;
import com.threeox.ormlibrary.entity.SQLExecuteType;
import com.threeox.ormlibrary.entity.TableMsg;
import com.threeox.ormlibrary.factory.DataBaseFactory;
import com.threeox.ormlibrary.inter.ISqlExecutor;
import com.threeox.ormlibrary.inter.OnQueryListListener;
import com.threeox.ormlibrary.inter.OnQueryListener;
import com.threeox.ormlibrary.inter.OnSqlListener;
import com.threeox.ormlibrary.util.SqlHelper;
import com.threeox.ormlibrary.util.TableUtil;
import com.threeox.utillibrary.util.Basic2ObjUtil;
import com.threeox.utillibrary.util.EmptyUtils;
import com.threeox.utillibrary.util.HandlerUtils;
import com.threeox.utillibrary.util.HandlerUtils.OnHandlerListener;
import com.threeox.utillibrary.util.LogUtils;
import com.threeox.utillibrary.util.java.ReflectUtil;
import com.threeox.utillibrary.util.thread.ThreadUtil;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 *
 * @ClassName: SqlExecutor
 *
 * @Description: Todo()
 *
 * @author 赵屈犇
 *
 * @date 创建时间:2017/4/25 下午11:54
 *
 * @version 1.0
 */
@TargetApi(Build.VERSION_CODES.HONEYCOMB)
public class SqlExecutor implements ISqlExecutor, OnHandlerListener {

    public static final String TAG = SqlExecutor.class.getName();

    private static SqlExecutor inst = null;

    // 执行SQL成功,               执行SQL失败
    private int sqlSuccess = 4,   sqlFailed = 5;
    // 查询单条数据成功                 查询单条数据失败
    private int querySuccess = 2, queryFailed = 3;
    // 查询多条数据成功                 查询多条数据失败
    private int queryListSuccess = 0, queryListFailed = 1;

    private HandlerUtils mHandlerUtils;

    private DataBaseFactory mDataBaseFactory = null;
    private SQLiteDatabase mWritableDatabase = null;
    private SQLiteDatabase mReadableDatabase = null;
    private OrmDatabaseManager mDatabaseManager = null;

    private OnSqlListener mSqlListener = null;
    private OnQueryListener mQueryListener = null;
    private OnQueryListListener mQueryListListener = null;

    private int SAVE = 6, SAVELIST = 7, UPDATE = 8, REMOVE = 9, REMOVEALL = 10, OTHER = 11, EXECUTESQL = 12;

    public static SqlExecutor newInstance() {
        return new SqlExecutor();
    }

    protected SqlExecutor() {
        try {
            mDataBaseFactory = DataBaseFactory.getInstance();
            mDatabaseManager = OrmDatabaseManager.getInstance();
            mHandlerUtils = HandlerUtils.getInstance().setOnHandlerListener(this);
        } catch (Exception e) {
        }
    }

    @Override
    public ISqlExecutor beginTransaction() {
        getWritableDatabase().beginTransaction();
        return this;
    }

    @Override
    public ISqlExecutor setTransactionSuccessful() {
        getWritableDatabase().setTransactionSuccessful();
        // mDatabase = null;
        return this;
    }

    @Override
    public ISqlExecutor endTransaction() {
        getWritableDatabase().endTransaction();
        mWritableDatabase = null;
        return this;
    }

    @Override
    public Exception executeSql(final String sql,final Object... bindArgs) {
        if (null == mSqlListener) {
            try {
                getWritableDatabase().execSQL(sql, bindArgs);
                return null;
            } catch (Exception e) {
                LogUtils.e(TAG, "executeSql报错啦:" + e.getMessage());
                return e;
            }
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        getWritableDatabase().execSQL(sql, bindArgs);
                        mHandlerUtils.putWhat(sqlSuccess).putArg1(EXECUTESQL);
                    } catch (Exception e) {
                        LogUtils.e(TAG, "executeSql报错啦:" + e.getMessage());
                        mHandlerUtils.putWhat(sqlFailed).putArg1(EXECUTESQL).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public Long save(final Object data, final String tableName) {
        if (null == mSqlListener) {
            if (null == data) {
                return null;
            }
            try {
                ContentValues contentValues = objToValues(data, tableName);
                return getWritableDatabase().insert(tableName, null, contentValues);
            } catch (Exception e) {
                LogUtils.e(TAG, tableName + "表添加失败!");
                return null;
            }
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    if (null == data) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(SAVE).put("数据为空!");
                    } else {
                        try {
                            ContentValues contentValues = objToValues(data, tableName);
                            Object result = getWritableDatabase().insert(tableName, null, contentValues);
                            mHandlerUtils.putWhat(sqlSuccess).putArg1(SAVE).put(result);
                        } catch (Exception e) {
                            mHandlerUtils.putWhat(sqlFailed).putArg1(SAVE).put(e.getMessage());
                        }
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public List<Long> saveList(final List datas, final String tableName) {
        if (null == mSqlListener) {
            List<Long> results = null;
            Long startTime = System.currentTimeMillis();
            LogUtils.d(tableName + "开始插入" + datas.size() + "条数据,开始时间:" + startTime);
            if (EmptyUtils.isNotEmpty(datas)) {
                results = new ArrayList<Long>();
                try {
                    beginTransaction();
                    for (Object data : datas) {
                        ContentValues contentValues = objToValues(data, tableName);
                        Long result = getWritableDatabase().insert(tableName, null, contentValues);
                        results.add(result);
                    }
                    setTransactionSuccessful();
                } catch (Exception e) {
                    LogUtils.e(TAG, "saveList报错了:" + e.getMessage());
                    results = null;
                } finally {
                    endTransaction();
                }
            }
            Long stopTime = System.currentTimeMillis();
            LogUtils.d(tableName + "结束插入" + datas.size() + "条数据,结束时间:" + stopTime);
            LogUtils.d(tableName + "用时:" + (stopTime - startTime));
            return results;
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    if (EmptyUtils.isEmpty(datas)) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(SAVELIST).put("数据为空!");
                    } else {
                        List<Long> results = new ArrayList<Long>();
                        try {
                            beginTransaction();
                            for (Object data : datas) {
                                ContentValues contentValues = objToValues(data, tableName);
                                Long result = getWritableDatabase().insert(tableName, null, contentValues);
                                results.add(result);
                            }
                            setTransactionSuccessful();
                            mHandlerUtils.putWhat(sqlSuccess).putArg1(SAVELIST).put(results);
                        } catch (Exception e) {
                            LogUtils.e(TAG, "saveList报错了:" + e.getMessage());
                            mHandlerUtils.putWhat(sqlFailed).putArg1(SAVELIST).put(e.getMessage());
                        } finally {
                            endTransaction();
                        }
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public Integer deleteAll(final String tableName) {
        if (null == mSqlListener) {
            return delete(tableName, null);
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        Integer result = getWritableDatabase().delete(tableName, null, null);
                        mHandlerUtils.putWhat(sqlSuccess).putArg1(REMOVEALL).put(result);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(REMOVEALL).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public Integer delete(final String tableName, final String whereClause, final String... whereArgs) {
        if (null == mSqlListener) {
            try {
                return getWritableDatabase().delete(tableName, whereClause, whereArgs);
            } catch (Exception e) {
                return null;
            }
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        Integer result = getWritableDatabase().delete(tableName, whereClause, whereArgs);
                        mHandlerUtils.putWhat(sqlSuccess).putArg1(REMOVE).put(result);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(REMOVE).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public Integer update(final String tableName,final Object data, final String whereClause,final String... whereArgs) {
        if (null == mSqlListener) {
            try {
                ContentValues values = objToValues(data, tableName);
                return getWritableDatabase().update(tableName, values, whereClause, whereArgs);
            } catch (Exception e) {
                return null;
            }
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        ContentValues values = objToValues(data, tableName);
                        Integer result = getWritableDatabase().update(tableName, values, whereClause, whereArgs);
                        mHandlerUtils.putWhat(sqlSuccess).putArg1(UPDATE).put(result);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(UPDATE).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public Integer update(final String tableName, final ContentValues values, final String whereClause, final String... whereArgs) {
        if (null == mSqlListener) {
            try {
                return getWritableDatabase().update(tableName, values, whereClause, whereArgs);
            } catch (Exception e) {
                return null;
            }
        } else {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        Integer result = getWritableDatabase().update(tableName, values, whereClause, whereArgs);
                        mHandlerUtils.putWhat(sqlSuccess).putArg1(UPDATE).put(result);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(sqlFailed).putArg1(UPDATE).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        }
    }

    @Override
    public <T> List<T> queryList(final String sql,final Class clazz, final String... selectionArgs) {
        if (null != mQueryListListener) {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        Cursor cursor = getReadableDatabase().rawQuery(sql, selectionArgs);
                        List list = cursorToList(cursor, clazz);
                        mHandlerUtils.putWhat(queryListSuccess).put(list);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(queryListFailed).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        } else {
            try {
                Cursor cursor = getReadableDatabase().rawQuery(sql, selectionArgs);
                return cursorToList(cursor, clazz);
            } catch (Exception e) {
                LogUtils.e(TAG, "queryList报错了:" + e.getMessage());
                return null;
            }
        }
    }

    @Override
    public <T> T query(final String sql, final Class clazz, final String... selectionArgs) {
        if (EmptyUtils.isNotEmpty(mQueryListener)) {
            ThreadUtil.runInThread(new Runnable() {
                @Override
                public void run() {
                    try {
                        T data = queryOne(sql, clazz, selectionArgs);
                        mHandlerUtils.putWhat(querySuccess).put(data);
                    } catch (Exception e) {
                        mHandlerUtils.putWhat(queryFailed).put(e.getMessage());
                    }
                    mHandlerUtils.send();
                }
            });
            return null;
        } else {
            try {
                return queryOne(sql, clazz, selectionArgs);
            } catch (Exception e) {
                return null;
            }
        }
    }

    private <T> T queryOne(String sql, Class clazz, String... selectionArgs) throws Exception {
        Cursor cursor = getReadableDatabase().rawQuery(sql, selectionArgs);
        if (cursor == null) {
            return null;
        }
        if (cursor.getCount() == 0) {
            cursor.close();
            return null;
        }
        cursor.moveToNext();
        T result = (T) cursorToObj(cursor, clazz);
        cursor.close();
        return result;
    }

    @Override
    public Long queryCount(String sql, String... selectionArgs) {
        Long count = null;
        sql = SqlHelper.getSqlCount(sql);
        if (EmptyUtils.isNotEmpty(sql)) {
            Cursor cursor = getReadableDatabase().rawQuery(sql, selectionArgs);
            if (cursor.moveToFirst()) {
                count = cursor.getLong(0);
            }
            cursor.close();
            return count;
        }
        return count;
    }

    @Override
    public ISqlExecutor setSQLiteDatabase(SQLiteDatabase db) {
        this.mWritableDatabase = db;
        this.mReadableDatabase = db;
        return this;
    }

    @Override
    public ISqlExecutor setOnSqlListener(OnSqlListener mSqlListener) {
        this.mSqlListener = mSqlListener;
        return this;
    }

    @Override
    public ISqlExecutor setOnQueryListener(OnQueryListener queryListener) {
        this.mQueryListener = queryListener;
        return this;
    }

    @Override
    public ISqlExecutor setOnQueryListListener(OnQueryListListener queryListListener) {
        this.mQueryListListener = queryListListener;
        return this;
    }

    /**
     * 将Cursor 反射为集合
     *
     * @param cursor
     * @param clazz
     * @return
     */
    public List cursorToList(Cursor cursor, Class clazz) {
        if (cursor == null) {
            return null;
        }
        if (cursor.getCount() == 0) {
            cursor.close();
            return null;
        }
        List list = new ArrayList();
        while (cursor.moveToNext()) {
            list.add(cursorToObj(cursor, clazz));
        }
        cursor.close();
        return list;
    }

    /**
     * 将Cursor 反射为对象
     *
     * @param cursor
     * @param clazz
     * @return
     */
    public Object cursorToObj(Cursor cursor, Class clazz) {
        try {
            // 得到列的数组
            String[] columnNames = cursor.getColumnNames();
            // 实例化对象
            Object newInstance = clazz.newInstance();
            if (newInstance instanceof Map) {
                for (String columnName : columnNames) {
                    Object result = getCursorValue(cursor, columnName);
                    ((JSONObject) newInstance).put(columnName, result);
                }
            } else {
                for (String columnName : columnNames) {
                    try {
                        Field field = ReflectUtil.getObjectField(clazz, columnName);
                        if (field != null) {
                            Class<? extends Object> typeClass = Basic2ObjUtil.getInstance().getBasicClass(field.getType());
                            Object result = getCursorValue(cursor, columnName);
                            if (!Basic2ObjUtil.getInstance().isBasicType(typeClass)) {
                                try {
                                    if (typeClass.getSimpleName().equals("List")) {
                                        result = JSON.parseArray(result.toString());
                                    } else {
                                        result = JSON.parseObject(result.toString(), typeClass);
                                    }
                                    LogUtils.d(TAG, "注入" + typeClass + "对象成功");
                                } catch (Exception e) {
                                    LogUtils.e(TAG, "注入" + typeClass + "对象失败!数据库保存数据请传入JSON" + result);
                                }
                            }
                            if (result != null) {
                                if (typeClass.equals(Integer.class)) {
                                    result = Integer.valueOf(String.valueOf(result));
                                } else if (typeClass.equals(Float.class)) {
                                    result = Float.valueOf(String.valueOf(result));
                                }
                                field.setAccessible(true);
                                field.set(newInstance, result);
                            }
                        }
                    } catch (Exception e) {
                        LogUtils.e(TAG, "反射对象的列字段出现异常:" + e.getMessage());
                    }
                }
            }
            return newInstance;
        } catch (Exception e) {
            LogUtils.e(TAG, e.getMessage());
        }
        return null;
    }

    /**
     * 得到Cursor的值
     *
     * @param cursor
     * @param columnName
     * @return
     */
    private Object getCursorValue(Cursor cursor, String columnName) {
        try {
            return getCursorValue(cursor, cursor.getColumnIndex(columnName));
        } catch (Exception e) {
            LogUtils.e(TAG, "getCursorValue报错了:" + e.getMessage());
        }
        return null;
    }

    /**
     * 得到Cursor的值
     *
     * @param cursor
     * @param columnIndex
     * @return
     */
    private Object getCursorValue(Cursor cursor, int columnIndex) {
        Object result = null;
        try {
            int cursorType = cursor.getType(columnIndex);
            if (Cursor.FIELD_TYPE_BLOB == cursorType) {
                result = cursor.getBlob(columnIndex);
            } else if (Cursor.FIELD_TYPE_FLOAT == cursorType) {
                result = cursor.getDouble(columnIndex);
            } else if (Cursor.FIELD_TYPE_INTEGER == cursorType) {
                result = cursor.getLong(columnIndex);
            } else if (Cursor.FIELD_TYPE_NULL == cursorType) {
                result = null;
            } else if (Cursor.FIELD_TYPE_STRING == cursorType) {
                result = cursor.getString(columnIndex);
            } else {
                result = cursor.getString(columnIndex);
            }
        } catch (Exception e) {
            LogUtils.e(TAG, "getCursorValue报错了:" + e.getMessage());
        }
        return result;
    }

    /**
     * ContentValues 转为对象
     *
     * @param values
     * @param obj
     * @return
     */
    public <T extends Object> T valueToObj(ContentValues values, Object obj) {
        try {
            if (values != null) {
                Class<? extends Object> clazz = obj.getClass();
                Set<String> set = values.keySet();
                for (String key : set) {
                    try {
                        Field field = ReflectUtil.getObjectField(clazz, key);
                        if (field != null) {
                            field.setAccessible(true);
                            field.set(obj, values.get(key));
                        }
                    } catch (Exception e) {
                        LogUtils.e(TAG, "valueToObj注入Field报错了:" + e.getMessage());
                    }
                }
                return (T) obj;
            }
        } catch (Exception e) {
            LogUtils.e(TAG, "valueToObj:报错了:" + e.getMessage());
        }
        return (T) obj;
    }

    /**
     * 根据对象转为ContentValues
     *
     * @param data
     * @param tableName
     * @return
     */
    private ContentValues objToValues(Object data, String tableName) {
        TableMsg tableMessage = mDataBaseFactory.getTableMessage(getReadableDatabase(), tableName);
        // TableMsg tableMsg = TableUtil.getTableMsgByKey(tableName);
        if (tableMessage == null)
            return null;
        String[] columnNames = tableMessage.getColumnNames();
        Class<?> clazz = data.getClass();
        Map mapData = objToMap(data, clazz);
        ContentValues contentValues = new ContentValues();
        for (String columnName : columnNames) {
            try {
                Object result = mapData.get(columnName);
                if (result != null) {
                    if (result instanceof Integer) {
                        contentValues.put(columnName, (Integer) result);
                    } else if (result instanceof Long) {
                        contentValues.put(columnName, (Long) result);
                    } else if (result instanceof Float) {
                        contentValues.put(columnName, (Float) result);
                    } else if (result instanceof Byte) {
                        contentValues.put(columnName, (Byte) result);
                    } else if (result instanceof Short) {
                        contentValues.put(columnName, (Short) result);
                    } else if (result instanceof Double) {
                        contentValues.put(columnName, (Double) result);
                    } else if (result instanceof String) {
                        contentValues.put(columnName, (String) result);
                    } else if (result instanceof Boolean) {
                        contentValues.put(columnName, (Boolean) result);
                    } else if ("byte[]".equals(result.getClass().getSimpleName())) {
                        contentValues.put(columnName, (byte[]) result);
                    } else if (result instanceof Object) {
                        contentValues.put(columnName, JSON.toJSONString(result));
                    } else {
                        LogUtils.d(TAG, columnName + "插入失败:save");
                    }
                } else {
                    LogUtils.d(TAG, columnName + "没有值:save");
                }
            } catch (Exception e) {
                e.printStackTrace();
                LogUtils.e(TAG, "save方法:" + e.getMessage());
            }
        }
        return contentValues;
    }

    /**
     * 将对象转为JSONObject
     *
     * @param data
     * @param clazz
     * @return
     */
    private Map objToMap(Object data, Class clazz) {
        if (data instanceof Map) {
            return (Map) data;
        } else {
            Map<String, Object> result = new HashMap<String, Object>();
            Field[] fields = clazz.getDeclaredFields();
            if (EmptyUtils.isNotEmpty(fields)) {
                for (Field field : fields) {
                    try {
                        String key = field.getName();
                        Column column = field.getAnnotation(Column.class);
                        if (column != null) {
                            String columnName = column.columnName();
                            if (EmptyUtils.isNotEmpty(columnName) && !columnName.equals(key)) {
                                key = column.columnName();
                            }
                        } else {
                            Primary primary = field.getAnnotation(Primary.class);
                            if (primary != null) {
                                String columnName = primary.columnName();
                                if (EmptyUtils.isNotEmpty(columnName) && !columnName.equals(key)) {
                                    key = primary.columnName();
                                }
                            }
                        }
                        field.setAccessible(true);
                        result.put(key, field.get(data));
                    } catch (Exception e) {
                        LogUtils.e("objToMap: 报错了:" + e.getMessage());
                    }
                }
                Class superClazz = clazz.getSuperclass();
                if (superClazz != null) {
                    Map map = objToMap(data, superClazz);
                    result.putAll(map);
                }
            }
            return result;
        }
    }

    @Override
    public void onHandleMessage(Message msg) {
        int what = msg.what;
        Object data = msg.obj;
        if (queryListSuccess == what) {// 查询集合成功
            if (null != mQueryListListener) {
                mQueryListListener.queryListSuccess((List) data);
            }
        } else if (queryListFailed == what) { // 查询集合失败
            if (null != mQueryListListener) {
                mQueryListListener.queryListFailed((String) data);
            }
        } else if (querySuccess == what) { // 查询对象成功
            if (null != mQueryListener) {
                mQueryListener.querySuccess(data);
            }
        } else if (queryFailed == what) { // 查询对象失败
            if (null != mQueryListener) {
                mQueryListener.queryFailed((String) data);
            }
        } else if (sqlSuccess == what) { // 执行SQL成功
            if (null != mSqlListener) {
                mSqlListener.success(getExecuteType(msg.arg1), data);
            }
        } else if (sqlFailed == what) { // 执行SQL失败
            if (null != mSqlListener) {
                mSqlListener.failed(getExecuteType(msg.arg1), data);
            }
        }
    }

    private SQLExecuteType getExecuteType(int type) {
        if (SAVE == type) {
            return SQLExecuteType.SAVE;
        } else if (SAVELIST == type) {
            return SQLExecuteType.SAVELIST;
        } else if (REMOVE == type) {
            return SQLExecuteType.REMOVE;
        } else if (REMOVEALL == type) {
            return SQLExecuteType.REMOVEALL;
        } else if (UPDATE == type) {
            return SQLExecuteType.UPDATE;
        } else if (EXECUTESQL == type) {
            return SQLExecuteType.EXECUTESQL;
        } else {
            return SQLExecuteType.OTHER;
        }
    }

    /**
     * 得到写的Database
     *
     * @return
     */
    public SQLiteDatabase getWritableDatabase() {
        if (mWritableDatabase == null)
            mWritableDatabase = mDatabaseManager.getWritableDatabase();
        return mWritableDatabase;
    }

    /**
     * 得到读的Database
     *
     * @return
     */
    public SQLiteDatabase getReadableDatabase() {
        if (mReadableDatabase == null)
            return mDatabaseManager.getReadableDatabase();
        return mReadableDatabase;
    }

}
